package com.shangyueshang.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.shangyueshang.model.GoodsStorageFee;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Component;

import java.util.List;
import java.util.Map;

/**
 * @author: JokeyZheng
 * @email: zhengjingfeng@ruqimobility.com
 * @created: 2023/5/16 15:00
 * @version: v1.0.0
 */
@Component
public interface GoodsStorageFeeMapper extends BaseMapper<GoodsStorageFee> {

    /**
     * 查询商品仓储费用
     *
     * @param params
     * @return
     */
    @Select("<script>" +
            "   SELECT  " +
            "       shop, " +
            "       sku, " +
            "       asin, " +
            "       trade_month, " +
            "       count(id) as quantity, " +
            "       round(sum(estimated_monthly_storage_fee * exchange_rate),2) as estimated_monthly_storage_fee, " +
            "       sum(weight) as weight, " +
            "       weight_units, " +
            "       sum(item_volume) as item_volume, " +
            "       volume_units, " +
            "       product_size_tier, " +
            "       sum(estimated_total_item_volume) as estimated_total_item_volume, " +
            "       sum(total_incentive_fee_amount * exchange_rate) as total_incentive_fee_amount, " +
            "       sum(breakdown_incentive_fee_amount * exchange_rate) as breakdown_incentive_fee_amount " +
            "   FROM " +
            "       goods_storage_fee " +
            "   <where>" +
            "       <if test='params.shop != null'> " +
            "		 AND shop IN " +
            "			<foreach collection='params.shop' item='item' open='(' separator=',' close=')'>" +
            "				#{item} " +
            "			</foreach>" +
            "       </if> " +
            "       <if test='params.sku != null'> " +
            "           AND sku=#{params.sku} " +
            "       </if> " +
            "       <if test='params.asin != null'> " +
            "           AND asin=#{params.asin} " +
            "       </if> " +
            "       <if test='params.trade_month != null'> " +
            "           AND trade_month=#{params.trade_month} " +
            "       </if> " +
            "   </where>" +
            "   group by shop, sku, trade_month " +
            "   order by trade_month desc, estimated_monthly_storage_fee desc " +
            "</script>")
    List<GoodsStorageFee> queryGoodsStorageFee(@Param("params") Map<String, Object> params);

}
